package org.cjlm.muaction.ds;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据库初始化工具类
 * 负责创建元数据表和插入样例数据
 */
public class DbInitializer {

    // 实体定义表SQL
    private static final String CREATE_META_COMP_TABLE_SQL =
            "CREATE TABLE meta_comp (\n" +
                    "    ID CHAR(20) NOT NULL PRIMARY KEY,\n" +
                    "    code VARCHAR(100) NOT NULL UNIQUE,\n" +
                    "    name VARCHAR(100) NOT NULL\n" +
                    ")";

    // 字段定义表SQL
    private static final String CREATE_META_ENTITY_TABLE_SQL =
            "CREATE TABLE meta_entity (\n" +
                    "    ID CHAR(20) NOT NULL PRIMARY KEY,\n" +
                    "    compid CHAR(20) NOT NULL,\n" +
                    "    code VARCHAR(100) NOT NULL,\n" +
                    "    name VARCHAR(100) NOT NULL,\n" +
                    "    clazzname VARCHAR(200) NOT NULL,\n" +
                    "    dbname VARCHAR(150) NOT NULL,\n" +
                    "    ismain CHAR(1) NOT NULL,\n" +
                    "    isdoc CHAR(1) NOT NULL\n" +
                    ")";

    // 表单布局表SQL
    private static final String CREATE_META_ATTRIBUTE_TABLE_SQL =
            "CREATE TABLE meta_attribute (\n" +
                    "    ID CHAR(20) NOT NULL PRIMARY KEY,\n" +
                    "    compid CHAR(20) NOT NULL,\n" +
                    "    entityid CHAR(20) NOT NULL,\n" +
                    "    code VARCHAR(100) NOT NULL,\n" +
                    "    name VARCHAR(100) NOT NULL,\n" +
                    "    parentcode VARCHAR(100) NULL,\n" +
                    "    type VARCHAR(100) NOT NULL,\n" +
                    "    dbtype VARCHAR(100) NOT NULL,\n" +
                    "    dblength int,\n" +
                    "    dbjd int,\n" +
                    "    doccompid CHAR(20) NULL,\n" +
                    "    enmu VARCHAR(2000) NULL,\n" +
                    "    isprikey CHAR(1) NOT NULL\n" +
                    ")";

    /**
     * 初始化数据库结构
     * @param conn 数据库连接对象
     */
    public static void initialize(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement())   {
            // 检查并创建元数据表
            if (!isTableExists(conn, "meta_comp")) {
                stmt.executeUpdate(CREATE_META_COMP_TABLE_SQL);
                stmt.executeUpdate(CREATE_META_ENTITY_TABLE_SQL);
                stmt.executeUpdate(CREATE_META_ATTRIBUTE_TABLE_SQL);

                // 插入示例模板数据
//                initSampleData(stmt);

                System.out.println("[INFO]  数据库初始化完成：创建3张元数据表，插入示例数据");
            } else {
                System.out.println("[INFO]  元数据表已存在，跳过初始化");
            }
        }
    }

    /**
     * 检查表是否存在
     * @param conn 数据库连接
     * @param tableName 表名称
     * @return 存在返回true，否则false
     */
    private static boolean isTableExists(Connection conn, String tableName) throws SQLException {
        DatabaseMetaData meta = conn.getMetaData();
        try (ResultSet rs = meta.getTables(null,  null, tableName, new String[]{"TABLE"})) {
            return rs.next();
        }
    }

    /**
     * 初始化示例数据
     * @param stmt Statement对象
     */
    private static void initSampleData(Statement stmt) throws SQLException {
        // 插入主实体（客户）
        stmt.executeUpdate(
                "INSERT INTO meta_entity (entity_name, is_master, description) VALUES " +
                        "('Customer', 1, '客户信息主表')",
                Statement.RETURN_GENERATED_KEYS
        );

        // 获取生成的实体ID
        int customerId;
        try (ResultSet rs = stmt.getGeneratedKeys())  {
            if (rs.next())  customerId = rs.getInt(1);
            else throw new SQLException("创建主实体失败");
        }

        // 插入客户表字段
        stmt.executeUpdate(
                "INSERT INTO meta_field (entity_id, field_name, display_name, data_type, is_primary, ui_component) VALUES " +
                        "(" + customerId + ", 'cust_id', '客户ID', 'INT', 1, 'TEXT')," +
                        "(" + customerId + ", 'name', '姓名', 'STRING', 0, 'TEXT')," +
                        "(" + customerId + ", 'email', '邮箱', 'STRING', 0, 'TEXT')," +
                        "(" + customerId + ", 'birthday', '生日', 'DATE', 0, 'DATE_PICKER')," +
                        "(" + customerId + ", 'vip', 'VIP会员', 'BOOLEAN', 0, 'CHECKBOX')"
        );

        // 插入子实体（订单）
        stmt.executeUpdate(
                "INSERT INTO meta_entity (entity_name, is_master, master_entity_id, description) VALUES " +
                        "('Order', 0, " + customerId + ", '订单子表')",
                Statement.RETURN_GENERATED_KEYS
        );

        // 获取生成的子实体ID
        int orderId;
        try (ResultSet rs = stmt.getGeneratedKeys())  {
            if (rs.next())  orderId = rs.getInt(1);
            else throw new SQLException("创建子实体失败");
        }

        // 插入订单表字段
        stmt.executeUpdate(
                "INSERT INTO meta_field (entity_id, field_name, display_name, data_type, is_primary, ui_component) VALUES " +
                        "(" + orderId + ", 'order_id', '订单ID', 'INT', 1, 'TEXT')," +
                        "(" + orderId + ", 'product', '产品名称', 'STRING', 0, 'TEXT')," +
                        "(" + orderId + ", 'amount', '金额', 'FLOAT', 0, 'TEXT')," +
                        "(" + orderId + ", 'order_date', '下单日期', 'DATE', 0, 'DATE_PICKER')"
        );

        // 插入默认列表布局配置（XML格式）
        String listLayoutConfig =
                "<layout>" +
                        "  <columns>" +
                        "    <column field=\"name\" width=\"120\" title=\"客户名称\"/>" +
                        "    <column field=\"email\" width=\"150\" title=\"电子邮箱\"/>" +
                        "    <column field=\"vip\" width=\"80\" title=\"VIP会员\"/>" +
                        "  </columns>" +
                        "</layout>";

        stmt.executeUpdate(
                "INSERT INTO form_layout (entity_id, layout_name, layout_type, config_data, is_default) VALUES " +
                        "(" + customerId + ", '默认列表', 'LIST', '" + listLayoutConfig + "', 1)"
        );

        // 插入卡片布局配置
        String cardLayoutConfig =
                "<layout>" +
                        "  <sections>" +
                        "    <section title=\"基本信息\">" +
                        "      <field name=\"name\" label=\"客户姓名\" type=\"TEXT\" x=\"10\" y=\"10\"/>" +
                        "      <field name=\"birthday\" label=\"出生日期\" type=\"DATE_PICKER\" x=\"10\" y=\"40\"/>" +
                        "    </section>" +
                        "    <section title=\"联系信息\">" +
                        "      <field name=\"email\" label=\"电子邮箱\" type=\"TEXT\" x=\"10\" y=\"80\"/>" +
                        "    </section>" +
                        "  </sections>" +
                        "</layout>";

        stmt.executeUpdate(
                "INSERT INTO form_layout (entity_id, layout_name, layout_type, config_data) VALUES " +
                        "(" + customerId + ", '名片视图', 'CARD', '" + cardLayoutConfig + "')"
        );
    }

    /**
     * 重置数据库（开发环境使用）
     * @param conn 数据库连接
     */
    public static void resetDatabase(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement())  {
            // 禁用外键约束检查
            stmt.execute("EXEC  sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'");

            // 删除所有表
            stmt.execute("EXEC  sp_msforeachtable 'DROP TABLE ?'");

            System.out.println("[INFO]  数据库已重置");
        } finally {
            // 重新启用约束检查
            try (Statement stmt = conn.createStatement())  {
                stmt.execute("EXEC  sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'");
            }
        }
    }
}
